﻿CREATE TRIGGER [dbo].[tri_Note]
ON [dbo].[t_Note]
FOR INSERT
AS
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON
IF EXISTS(
	SELECT * FROM inserted
	WHERE NOT VantageID IS NULL OR
	StatusID = dbo.fs_StateID__Notes__ExportedFromUSIX()
	) RETURN


--DECLARE @NoteUser sysname SELECT @NoteUser = NoteUser FROM inserted
--RAISERROR('NoteUser:%s',16,1,@NoteUser)BEGIN ROLLBACK RETURN END

UPDATE N SET StatusID = dbo.fs_StateID_Start_Note()
FROM t_Note N INNER JOIN inserted i ON N.ID = i.ID
WHERE N.StatusID = 0

DELETE N_L FROM t_Note_Last N_L INNER JOIN inserted i ON N_L.LoanID = i.LoanID /*AND N_L.NoteTypeID = i.NoteTypeID AND i.IsResolved = 1*/
INSERT INTO t_Note_Last(LoanID,NoteID,NoteTypeID)
SELECT N.LoanID,N.ID,N.NoteTypeID
FROM t_Note N INNER JOIN
(
SELECT N.LoanID,MAX(N.NoteDate)NoteDate,N.NoteTypeID
FROM t_Note N INNER JOIN inserted i ON i.LoanID = N.LoanID-- AND i.NoteTypeID = N.NoteTypeID
WHERE N.NoteUser IN (SELECT DISTINCT sAMAccountName FROM v_ADS_Collector) AND N.IsResolved = 1
GROUP BY N.LoanID , N.NoteTypeID
)
NL  ON N.LoanID = NL.LoanID AND N.NoteDate = NL.NoteDate AND NL.NoteTypeID = N.NoteTypeID


--SELECT LoanID,MAX(ID)ID,NoteTypeID
--FROM inserted WHERE NoteUser  IN (SELECT DISTINCT sAMAccountName FROM v_ADS_Collector) AND IsResolved = 1
--GROUP BY LoanID,NoteTypeID

DECLARE @SC int EXEC @SC = sp__Context_SecurityOff 1

UPDATE N SET DateDue = DATEADD(hh,9,dbo.Today(GETDATE()+COALESCE(CONVERT(int,dbo.fs_StateParamValue(N.StatusID,'NextDateDue_Days')),
																										CONVERT(int,dbo.fs_StateParamValue(SP.ID,'NextDateDue_Days')),3)))
FROM t_Note N INNER JOIN inserted i ON N.ID = i.ID
INNER JOIN t_State S ON S.ID = N.StatusID
INNER JOIN t_State SP ON SP.ID = S.ParentID
WHERE N.DateDue IS NULL

UPDATE N SET DateDueBy = dbo.UserName()
FROM t_Note N INNER JOIN inserted i ON N.ID = i.ID
WHERE NULLIF(N.DateDueBy,'') IS NULL

UPDATE N SET DateDue = DATEADD(hh,9,N.DateDue)
FROM t_Note N INNER JOIN inserted i ON N.ID = i.ID
WHERE DATEPART(hh,N.DateDue) = 0

UPDATE N SET DateDue = DATEADD(hh,9,dbo.Today(N.DateDue))
FROM t_Note N INNER JOIN inserted i ON N.ID = i.ID
WHERE DATEPART(hh,N.DateDue) > 18

DECLARE @Pattern sysname SET @Pattern = '\d\d\d\D{1,2}\d\d\d\D{1}\d\d\d\d'
UPDATE N SET Phone = dbo.clr_RegEx_MatchGroup(N.Note,'('+@Pattern+')',1)
FROM t_Note N INNER JOIN inserted i ON N.ID = i.ID
WHERE /*ISNULL(N.Phone,'') = '' AND */dbo.clr_RegEx_Match(N.Note,@Pattern,1) = 1

UPDATE N SET Note = ts_NoteActionCode.Name + ': ' + t_State.Name
FROM t_Note AS N
INNER JOIN inserted AS i ON N.ID = i.ID
INNER JOIN ts_NoteActionCode ON N.NoteActionCodeID = ts_NoteActionCode.ID
INNER JOIN t_State ON N.StatusID = t_State.ID
WHERE ISNULL(N.Note,'') = ''

UPDATE N SET IsCollection = B.InBucket
FROM t_Note N
INNER JOIN(
	SELECT i.ID,MAX(CONVERT(int,InBucket)) InBucket FROM inserted AS i
	INNER JOIN v_ADS_Collector C ON C.sAMAccountName  = i.NoteUser
	GROUP BY i.ID
)B ON N.ID = B.ID

EXEC sp__Context @SC



